USE [KCL]
GO

/****** Object:  UserDefinedFunction [dbo].[GetNextMaterialCode]    Script Date: 11/27/2011 18:31:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Chau Tieu
-- Create date: 2011-11-27
-- Description:	Get next material code
-- =============================================
CREATE FUNCTION [dbo].[GetNextMaterialCode] ()
RETURNS VARCHAR(10)
AS
BEGIN
	DECLARE @NextID INT;
	DECLARE @NextMaterialCode VARCHAR(10);
	
	SELECT @NextID = (ISNULL(MAX(CONVERT(INT, RIGHT(code, 8))), -1) + 1)
	FROM Material
	WHERE code LIKE 'NL%' 
		AND LEN(code) = 10
		AND ISNUMERIC(RIGHT(code, 8)) = 1;
		
	SET @NextMaterialCode = 'NL' + REPLICATE('0', 8 - LEN(@NextID)) + CONVERT(VARCHAR, @NextID);
	RETURN @NextMaterialCode;
END

GO

